﻿<%@ WebHandler Language="C#" Class="RbContainerShipRecord" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 热表分厂转工报表（旧）
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.3.24
 **/

public class RbContainerShipRecord : IHttpHandler
{
    public bool IsReusable { get { return false; } }
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    UserImage u = new UserImage();
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
SELECT DISTINCT f.factoryname fromfactory,f2.factoryname tofactory,
       cci.familylistnames,cci.productname,cci.productdescription,cci.containername,cci2.containername rbcontainername,
       cr.needcompletedate,cr.estimatedcompletedate,cr.senddate,cr.toacceptdate,e.fullname,csr.completedate,csr.recivedate,
       cci.qty,ws.workflowstepname,ws2.workflowstepname inspec,csr.acceptanceqty,
       CASE WHEN pdr.parentid IS NOT NULL THEN '转工拒收' 
            WHEN pdr.parentid IS NULL AND NVL(csr.recivedate,csr.completedate) IS NOT NULL THEN '已完成' 
            WHEN pdr.parentid IS NULL AND cr.toacceptdate IS NOT NULL AND NVL(csr.recivedate,csr.completedate) IS NULL THEN '待完成' 
            WHEN pdr.parentid IS NULL AND cr.toacceptdate IS NULL AND NVL(csr.recivedate,csr.completedate) IS NULL THEN '未接收' 
       END status,
       pdr.createdate denydate,
       CASE WHEN pdr.parentid IS NOT NULL THEN 0
            WHEN csr.completedate IS NULL THEN TRUNC(SYSDATE) - TRUNC(cr.needcompletedate)
            ELSE TRUNC(csr.completedate) - TRUNC(cr.needcompletedate)
       END delay,
       CASE WHEN csd2.containerstepdispatchid IS NOT NULL THEN '已派工' ELSE '未派工' END dispatchstatus
FROM containershiprecord cr
LEFT JOIN containershiprecive csr ON cr.containershiprecordid = csr.containershiprecordid
LEFT JOIN containercurrentinfo cci ON cr.containerid = cci.containerid
LEFT JOIN factory f ON f.factoryid = cr.factoryid --发出分厂
LEFT JOIN factory f2 ON f2.factoryid = cr.tofactoryid --承制分厂
LEFT JOIN employee e ON e.employeeid = cr.ToAcceptor --热表接收人
LEFT JOIN workflowstep ws ON ws.workflowstepid = cr.workflowstepid --转出工序
LEFT JOIN workflowstep ws2 ON ws2.workflowstepid = cr.toworkflowstepid --转入工序
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4) --转工拒收
LEFT JOIN employee de ON de.employeeid = pdr.employeeid --转工拒收人
LEFT JOIN containersubcontainer sub ON sub.containerid = cr.containerid AND sub.workflowstepid = cr.workflowstepid AND sub.isuse = 0--关联热表子卡
LEFT JOIN containercurrentinfo cci2 ON cci2.containerid = sub.subcontainerid
LEFT JOIN containerstepdispatch2 csd2 ON csd2.containerid = cci2.containerid AND csd2.isuse = 0
";

        if (jObject["specno"] != null && jObject["specno"].ToString() != "")
        {
            sql += @"
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
";
        }
        sql += @"
WHERE cr.isuse = 0 AND cci.containerstatus = 1-- AND cr.needcompletedate IS NOT NULL 
";
        //发出分厂
        if (jObject["fromfactoryid"] != null && jObject["fromfactoryid"].ToString() != "")
        {
            sql += string.Format(" AND cr.factoryid = '{0}'", jObject["fromfactoryid"].ToString());
        }

        //承制分厂
        if (jObject["tofactoryid"] != null && jObject["tofactoryid"].ToString() != "")
        {
            sql += string.Format(" AND cr.tofactoryid = '{0}'", jObject["tofactoryid"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //工序号
        if (jObject["specno"] != null && jObject["specno"].ToString() != "")
        {
            sql += string.Format(" AND s.specno LIKE '%{0}%'", jObject["specno"].ToString());
        }

        //转工状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "未接收")
            {
                sql += string.Format(" AND cr.toacceptdate IS NULL AND NVL(csr.recivedate,csr.completedate) IS NULL AND pdr.parentid IS NULL");
            }
            if (jObject["status"].ToString() == "待完成")
            {
                sql += string.Format(" AND cr.toacceptdate IS NOT NULL AND NVL(csr.recivedate,csr.completedate) IS NULL AND pdr.parentid IS NULL ");
            }
            if (jObject["status"].ToString() == "已完成")
            {
                sql += string.Format(" AND NVL(csr.recivedate,csr.completedate) IS NOT NULL AND pdr.parentid IS NULL");
            }
            if (jObject["status"].ToString() == "转工拒收")
            {
                sql += string.Format(" AND pdr.parentid IS NOT NULL ");
            }
        }

        //接收人（调度员）
        if (jObject["receiver"] != null && jObject["receiver"].ToString() != "")
        {
            sql += string.Format(" AND cr.ToAcceptor = '{0}'", jObject["receiver"].ToString());
        }

        //接收日期
        if (jObject["receivedate"] != null && jObject["receivedate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["receivedate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(cr.toacceptdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
                sql += string.Format(" AND TRUNC(cr.toacceptdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
            }
        }

        //要求完成时间
        if (jObject["needcompletedate"] != null && jObject["needcompletedate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["needcompletedate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND cr.needcompletedate >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
                sql += string.Format(" AND cr.needcompletedate <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
            }
        }

        //是否已入库
        if (jObject["isinstore"] != null)
        {
            if (jObject["isinstore"].ToString() == "已入库")
            {
                sql += " AND cci.isinstore = 1";
            }
            else
            {
                sql += " AND cci.isinstore = 0";
            }
        }

        //热表是否已派工
        if (jObject["isdispatch"] != null && jObject["isdispatch"].ToString() != "")
        {
            string isdispatch = jObject["isdispatch"].ToString();
            if (isdispatch == "已派工")
            {
                sql += " AND csd2.containerstepdispatchid IS NOT NULL";
            }
            else
            {
                sql += " AND csd2.containerstepdispatchid IS NULL";
            }
        }

        //热表批次号
        if (jObject["rbcontainername"] != null && jObject["rbcontainername"].ToString() != "")
        {
            sql += string.Format(" AND cci2.containername = '{0}'", jObject["rbcontainername"].ToString());
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataRow dr in dt.Rows)
        {
            if (dr["delay"].ToString() != "" && Convert.ToDouble(dr["delay"].ToString()) < 0)
            {
                dr["delay"] = 0;
            }
        }

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (dr["delay"].ToString() != "" && Convert.ToDouble(dr["delay"].ToString()) < 0)
            {
                dr["delay"] = 0;
            }
        }

        dtResult.Columns["fromfactory"].Caption = "主制分厂";
        dtResult.Columns["tofactory"].Caption = "承制分厂";
        dtResult.Columns["familylistnames"].Caption = "机型";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["workflowstepname"].Caption = "转出工序";
        dtResult.Columns["needcompletedate"].Caption = "要求完成时间";
        dtResult.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dtResult.Columns["senddate"].Caption = "转出时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["fullname"].Caption = "接收人";
        dtResult.Columns["completedate"].Caption = "完成时间";
        dtResult.Columns["recivedate"].Caption = "转入时间";
        dtResult.Columns["inspec"].Caption = "转入时间";
        dtResult.Columns["acceptanceqty"].Caption = "转入数量";
        dtResult.Columns["status"].Caption = "转工状态";
        dtResult.Columns["denydate"].Caption = "拒收时间";
        dtResult.Columns["delay"].Caption = "超期时间";
        dtResult.Columns["qty"].Caption = "转出数量";
        dtResult.Columns["rbcontainername"].Caption = "热表批次号";
        dtResult.Columns["dispatchstatus"].Caption = "热表是否已派工";
        ExportHelper.ExportExcel(dtResult, jObject["factoryname"].ToString() + "转工报表");
    }
}